﻿Imports System.Data.OleDb

Module ModulePostVentas

    Public Class C_todosPostVentas
        '**********************************************************************************************************************
        'Carga de opciones y valores de las caract a evaluar en la llamada
        '**********************************************************************************************************************

        '+++++++++++++ Correspondiente a la tabla saludo_pvta +++++++++++++
        Public Function optSaludo() As OleDbDataReader

            Using cadenaConexBD()
                Dim droptS As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_salu,opt_salu,vaop_salu from saludo_pvta", cadenaConexBD)
                droptS = instruccion.ExecuteReader()
                Return droptS
                droptS.Close()
            End Using
        End Function
        Public Function valorSalu(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_salu from saludo_pvta where opt_salu = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorSalu = CType(obj, String)
                If valorSalu Is Nothing Then
                    valorSalu = "Inexistente"
                End If
            Catch ex As Exception
                valorSalu = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        '+++++++++++++ Correspondiente a la tabla empatia_pvta +++++++++++++
        Public Function optEmpatia() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptEmpa As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_emp,opt_emp,vaop_emp from empatia_pvta", cadenaConexBD)
                droptEmpa = instruccion.ExecuteReader()
                Return droptEmpa
                droptEmpa.Close()
            End Using
        End Function
        Public Function valorEmpatia(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_emp from empatia_pvta where opt_emp = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorEmpatia = CType(obj, String)
                If valorEmpatia Is Nothing Then
                    valorEmpatia = "Inexistente"
                End If
            Catch ex As Exception
                valorEmpatia = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        '+++++++++++++ Correspondiente a la tabla escucha_pvta +++++++++++++
        Public Function optEscucha() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptind As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_esc,opt_esc,vaop_esc from escucha_pvta", cadenaConexBD)
                droptind = instruccion.ExecuteReader()
                Return droptind
                droptind.Close()
            End Using
        End Function
        Public Function valorEscucha(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_esc from escucha_pvta where opt_esc = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorEscucha = CType(obj, String)
                If valorEscucha Is Nothing Then
                    valorEscucha = "Inexistente"
                End If
            Catch ex As Exception
                valorEscucha = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        '+++++++++++++ Correspondiente a la tabla indagacion_pvta +++++++++++++
        Public Function optIndagacion() As OleDbDataReader

            Using cadenaConexBD()
                Dim droptmanejo As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_ind,opt_ind,vaop_ind from indagacion_pvta", cadenaConexBD)
                droptmanejo = instruccion.ExecuteReader()
                Return droptmanejo
                droptmanejo.Close()
            End Using
        End Function
        Public Function valorIndagacion(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_ind from indagacion_pvta where opt_ind = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorIndagacion = CType(obj, String)
                If valorIndagacion Is Nothing Then
                    valorIndagacion = "Inexistente"
                End If
            Catch ex As Exception
                valorIndagacion = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        '+++++++++++++ Correspondiente a la tabla disposicion_pvta +++++++++++++
        Public Function optDisposicion() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptComp As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_disp,opt_disp,vaop_disp from disposicion_pvta", cadenaConexBD)
                droptComp = instruccion.ExecuteReader()
                Return droptComp
                droptComp.Close()
            End Using
        End Function
        Public Function valorDisposicion(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_disp from disposicion_pvta where opt_disp = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorDisposicion = CType(obj, String)
                If valorDisposicion Is Nothing Then
                    valorDisposicion = "Inexistente"
                End If
            Catch ex As Exception
                valorDisposicion = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        '+++++++++++++ Correspondiente a la tabla precision_pvta +++++++++++++
        Public Function optPrecision() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptinfo As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_spe,opt_spe,vaop_spe from precision_pvta", cadenaConexBD)
                droptinfo = instruccion.ExecuteReader()
                Return droptinfo
                droptinfo.Close()
            End Using
        End Function
        Public Function valorPrecision(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_spe from precision_pvta where opt_spe = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorPrecision = CType(obj, String)
                If valorPrecision Is Nothing Then
                    valorPrecision = "Inexistente"
                End If
            Catch ex As Exception
                valorPrecision = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        '+++++++++++++ Correspondiente a la tabla comunicacion_pvta +++++++++++++
        Public Function optComunicacion() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptComuni As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_com,opt_com,vaop_com from comunicacion_pvta", cadenaConexBD)
                droptComuni = instruccion.ExecuteReader()
                Return droptComuni
                droptComuni.Close()
            End Using
        End Function
        Public Function valorComunicacion(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_com from comunicacion_pvta where opt_com = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorComunicacion = CType(obj, String)
                If valorComunicacion Is Nothing Then
                    valorComunicacion = "Inexistente"
                End If
            Catch ex As Exception
                valorComunicacion = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        '+++++++++++++ Correspondiente a la tabla cierre_pvta +++++++++++++
        Public Function optCierre() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptCi As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_cie,opt_cie,vaop_cie from cierre_pvta", cadenaConexBD)
                droptCi = instruccion.ExecuteReader()
                Return droptCi
                droptCi.Close()
            End Using
        End Function
        Public Function valorCierre(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_cie from cierre_pvta where opt_cie = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorCierre = CType(obj, String)
                If valorCierre Is Nothing Then
                    valorCierre = "Inexistente"
                End If
            Catch ex As Exception
                valorCierre = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

    End Class
    '**********************************************************************************************************************
    'Clase de insercion de datos en la base de datos
    '**********************************************************************************************************************
    Public Class C_agregarDatosPostVentas

        Public Sub guardarLLamada()
            Try
                cadenaConexBD()
                Using cadenaConexBD()
                    'id tipo de llamada 
                    Dim buscaridTllamada As New OleDbCommand("Select id_tllam from tipo_llamada where desc_tllam = '" & frmPosVentas.cbo_tllamada.SelectedItem & "'", cadenaConexBD)
                    Dim idtllamada = buscaridTllamada.ExecuteScalar()
                    'id de la empresa
                    Dim buscaridEmpresa As New OleDbCommand("Select id_empr from empresa where nomb_empr = '" & frmPosVentas.cbo_empresa.SelectedItem & "'", cadenaConexBD)
                    Dim idEmpresa = buscaridEmpresa.ExecuteScalar()
                    'id auditor 
                    Dim buscaridAuditor As New OleDbCommand("Select id_au from auditor where noap_au = '" & frmPosVentas.cbo_controla.SelectedItem & "'", cadenaConexBD)
                    Dim idAuditor = buscaridAuditor.ExecuteScalar()
                    'id auditado
                    Dim buscaridAuditado As New OleDbCommand("Select id_ado from auditado where noap_ado = '" & frmPosVentas.cbo_auditado.SelectedItem & "'", cadenaConexBD)
                    Dim idAuditado = buscaridAuditado.ExecuteScalar()
                    'inserccion de datos a la tabla
                    Dim instruccion As New OleDbCommand("Insert into llamada_pvta (fau_llam, fec_llam, nlot_llam, id_tllam, id_empr, id_au, id_ado, vaop_salu, vaop_emp, vaop_esc, vaop_ind, vaop_disp, vaop_pre, vaop_com, vaop_cie, tfav_llam, tctra_llam, idunico_llam, vato_llam,caso_llam) values ('" & frmPosVentas.dtp_hoy.Value & "','" & frmPosVentas.dtp_fllam.Value & "','" & (frmPosVentas.cboLoteLetra.Text + frmPosVentas.cboLoteNum.Text) & "'," & idtllamada & "," & idEmpresa & "," & idAuditor & "," & idAuditado & ",'" & frmPosVentas.txtv01.Text & "','" & frmPosVentas.txtv02.Text & "','" & frmPosVentas.txtv03.Text & "','" & frmPosVentas.txtv04.Text & "','" & frmPosVentas.txtv05.Text & "','" & frmPosVentas.txtv06.Text & "','" & frmPosVentas.txtv07.Text & "','" & frmPosVentas.txtv08.Text & "','" & frmPosVentas.rtxt_afavor.Text & "','" & frmPosVentas.rtxt_encontra.Text & "','" & frmPosVentas.lblIdunicoLlam.Text & "','" & frmPosVentas.lbl_puntaje.Text & "','" & frmPosVentas.txtCaso.Text & "' )", cadenaConexBD)
                    instruccion.ExecuteNonQuery()
                End Using
                MsgBox("Auditoria guardada con Éxito", MsgBoxStyle.Information, "Auditoria")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub

    End Class

    '**********************************************************************************************************************
    'Funciones para cargar opciones y valores a los combos del formulario de ventas
    '**********************************************************************************************************************
    Public Sub CargarOptSaluPVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optS As New C_todosPostVentas
        Dim droptS As OleDbDataReader = optS.optSaludo

        If droptS Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptS.Read()
            strloc = ""
            If Not droptS.IsDBNull(0) Then
                strloc = droptS.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptS.Close()
    End Sub
    Public Sub CargarOptEmpaPVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optE As New C_todosPostVentas
        Dim droptE As OleDbDataReader = optE.optEmpatia

        If droptE Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptE.Read()
            strloc = ""
            If Not droptE.IsDBNull(0) Then
                strloc = droptE.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptE.Close()
    End Sub
    Public Sub CargarOptEscPVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optind As New C_todosPostVentas
        Dim droptind As OleDbDataReader = optind.optEscucha
        If droptind Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptind.Read()
            strloc = ""
            If Not droptind.IsDBNull(0) Then
                strloc = droptind.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptind.Close()
    End Sub
    Public Sub CargarOptIndPVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optDisp As New C_todosPostVentas
        Dim droptDisp As OleDbDataReader = optDisp.optIndagacion
        If droptDisp Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptDisp.Read()
            strloc = ""
            If Not droptDisp.IsDBNull(0) Then
                strloc = droptDisp.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptDisp.Close()
    End Sub
    Public Sub CargarOptDisPVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optComp As New C_todosPostVentas
        Dim droptComp As OleDbDataReader = optComp.optDisposicion
        If droptComp Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptComp.Read()
            strloc = ""
            If Not droptComp.IsDBNull(0) Then
                strloc = droptComp.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptComp.Close()
    End Sub
    Public Sub CargarOptPresPVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optinfo As New C_todosPostVentas
        Dim droptInfo As OleDbDataReader = optinfo.optPrecision
        If droptInfo Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptInfo.Read()
            strloc = ""
            If Not droptInfo.IsDBNull(0) Then
                strloc = droptInfo.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptInfo.Close()
    End Sub
    Public Sub CargarOptComPVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optComun As New C_todosPostVentas
        Dim droptComun As OleDbDataReader = optComun.optComunicacion
        If droptComun Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptComun.Read()
            strloc = ""
            If Not droptComun.IsDBNull(0) Then
                strloc = droptComun.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptComun.Close()
    End Sub
    Public Sub CargarOptCierPVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optCredd As New C_todosPostVentas
        Dim droptCredd As OleDbDataReader = optCredd.optCierre
        If droptCredd Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptCredd.Read()
            strloc = ""
            If Not droptCredd.IsDBNull(0) Then
                strloc = droptCredd.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptCredd.Close()
    End Sub

End Module
